package com.ky.pro.java.fx.jdbc;

import java.sql.*;

/**
 * @ClassName com.ky.pro.java.fx.jdbc.JdbcRawMain
 * @Description 原生JDBC实现
 * @Date 2021/12/4 17:41
 * @Author kevin
 **/
public class JdbcRawMain {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {

        Class.forName("org.h2.Driver");

        Connection connection = null;
        Statement statement = null;

        try {
            connection = DriverManager.getConnection("jdbc:h2:mem:school");
            statement = connection.createStatement();

            DataUtil.initDB(statement);

            // 开启事务
            connection.setAutoCommit(false);

            // 添加班级和学生数据
            statement.executeUpdate("insert into t_school values(1, 'No.1 High School')");
            statement.executeUpdate("insert into t_klass values(1, '1-1', 1)");
            statement.executeUpdate("insert into t_klass values(2, '1-2', 1)");
            statement.executeUpdate("insert into t_student values(1, 'kevin', 16, 1, 1)");
            statement.executeUpdate("insert into t_student values(2, 'alice', 15, 0, 1)");
            statement.executeUpdate("insert into t_student values(3, 'bob', 17, 1, 2)");

            // 查询所有学生详情
            ResultSet rs = statement.executeQuery("select * from t_student st join" +
                    "(select s.id as sid, s.name as sname, k.id as kid, k.name as kname from t_school s join t_klass k on k.school_id=s.id) sk " +
                    "on st.klass_id = sk.kid");

            connection.commit();

            // 显示查询结果
            DataUtil.dumpQueryResult(rs);

        } catch (Exception e) {
            e.printStackTrace();
            // 回滚
            if (null != connection) {
                connection.rollback();
            }
        } finally {
            if (null != statement) {
                statement.close();
            }
            if (null != connection) {
                connection.setAutoCommit(true);
                connection.close();
            }
        }
    }
}
